﻿import postgresql
import os
import sys

sys.argv[1]

user="palo"
password="palo"
server="sa-serv8"
db_openerp=sys.argv[1]
db_log="log"

__dir__ = os.path.dirname(os.path.abspath(__file__))
path_error = os.path.join(__dir__, "clean_"+db_openerp+"_err.log")
sys.stderr = open(path_error, 'a')

conn_db = postgresql.open("pq://"+user+":"+password+"@"+server+"/"+db_openerp)
conn_log = postgresql.open("pq://"+user+":"+password+"@"+server+"/"+db_log)

insert_into_log = conn_log.prepare("INSERT INTO oe_1s_cleaning(s_db, s_table, _statement, s_number, s_comment, s_sql) VALUES ($1, $2, $3, $4, $5, $6)")

if 

# удаляем договоры "без подразделений"

account_analytic_account = db_new_production.prepare("DELETE FROM account_analytic_account WHERE x_1s_id IS NOT NULL AND manager_id NOT IN (SELECT resource_resource.user_id FROM resource_resource, hr_employee WHERE hr_employee.department_id IN (SELECT DISTINCT id FROM hr_department WHERE x_1s_id IS NOT NULL) AND hr_employee.resource_id=resource_resource.id)")

account_analytic_account_log=account_analytic_account()

insert_into_db_new_log = db_new_log.prepare("INSERT INTO oe_1s_cleaning(s_db, s_table, _statement, s_number) VALUES ($1, $2, $3, $4)")
insert_into_db_new_log('production', 'account_analytic_account', account_analytic_account_log[0], account_analytic_account_log[1])


# удаляем сотрудников "без договоров"

hr_employee = db_new_production.prepare("DELETE FROM hr_employee WHERE x_1s_sc11305_id IS NOT NULL AND resource_id IN (SELECT DISTINCT id FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account))")
hr_employee()


# удаляем ресурсы "без договоров"

resource_resource=db_new_production.prepare("DELETE FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)")
resource_resource()


# удаляем подразделения "без договоров"
		
hr_department=db_new_production.prepare("DELETE FROM hr_department WHERE x_1s_id IS NOT NULL AND id NOT IN (SELECT DISTINCT department_id FROM hr_employee WHERE x_1s_sc11305_id IS NOT NULL AND resource_id NOT IN (SELECT DISTINCT id FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)))")
hr_department()


# удаляем пользователей "без договоров"

res_users=db_new_production.prepare("DELETE FROM res_users WHERE x_1s_sc11305_id IS NOT NULL AND id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)")
res_users()
	

# удаляем контрагентов-пользователей "без договоров"

res_partner_user=db_new_production.prepare("DELETE FROM res_partner WHERE x_1s_sc11305_id IS NOT NULL AND id IN (SELECT DISTINCT partner_id FROM res_users WHERE x_1s_sc11305_id IS NOT NULL AND id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account))")
res_partner_user()

# удаляем контрагентов без контактов

res_partner_partner=db_new_production.prepare("DELETE FROM res_partner WHERE x_1s_name is not null AND id NOT IN (SELECT DISTINCT parent_id FROM res_partner WHERE x_1s_id IS NOT NULL)")
res_partner_partner()

sys.stderr.close()
#sys.stdout.close()

sys.stderr = sys.__stderr__
#sys.stdout = sys.__stdout__
